#! /usr/bin/env python
# -*- coding: utf-8 -*-

from ib.ext.Contract import Contract
from ib.opt import ibConnection, message
from time import sleep
from datetime import date
import pyodbc, os, sys

# print all messages from TWS
def watcher(msg):
    print msg

def makeStkContract(contractTuple):
    newContract = Contract()
    newContract.m_symbol = contractTuple[0]
    newContract.m_secType = contractTuple[1]
    newContract.m_exchange = contractTuple[2]
    newContract.m_currency = contractTuple[3]
    newContract.m_expiry = contractTuple[4]
    newContract.m_strike = contractTuple[5]
    newContract.m_right = contractTuple[6]
    print 'Contract Values:%s,%s,%s,%s,%s,%s,%s:' % contractTuple
    return newContract

def makeFutContract(symbol, exchange, expiry):
    newContract = Contract()
    newContract.m_symbol = symbol
    newContract.m_secType = 'FUT'
    newContract.m_exchange = exchange
    newContract.m_currency = 'USD'
    newContract.m_expiry = expiry
    newContract.m_includeExpired = True
    #newContract.m_strike = ''
    #newContract.m_right = ''
    return newContract

def printHistoricalData(msg):
    global fout, symbol
    if msg.date.find("finish") == -1:
        timeline = msg.date[:4] + '-' + msg.date[4:6] + '-' + msg.date[6:]
        print '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s' % (symbol, timeline , msg.open, msg.high, msg.low, msg.close, msg.volume, msg.count, msg.WAP, str(msg.WAP*msg.volume))
        fout.writelines('%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n' % (symbol, msg.date, msg.open, msg.high, msg.low, msg.close, msg.volume, msg.count, msg.WAP, str(msg.WAP*msg.volume)))

if __name__ == '__main__':
    cnxn = pyodbc.connect('DSN=Stock')
    cursor = cnxn.cursor()

    con = ibConnection()
    con.host = '69.120.108.218'
    #con.port = 168
    #con.registerAll(watcher)
    #con.unregister(watcher, message.HistoricalData)
    #con.register(printHistoricalData, message.HistoricalData)
    con.connect()
    sleep(1)
    tickId = 1
    fout = open("future_onemin_temp.txt", 'w')

    """
    getsymbol = "SELECT DISTINCT Symbol\
        FROM Stock.dbo.stock_onemin"
    cursor.execute(getsymbol)
    rows = cursor.fetchall()
    """

    ESH1 = makeFutContract('ES', 'GLOBEX', '20110318')
    ESM1 = makeFutContract('ES', 'GLOBEX', '20110617')
    NQH1 = makeFutContract('NQ', 'GLOBEX', '20110318')
    NQM1 = makeFutContract('NQ', 'GLOBEX', '20110617')
    YMH1 = makeFutContract('YM', 'ECBOT', '201103')
    YMM1 = makeFutContract('YM', 'ECBOT', '201106')
    futContract = ESH1

    symbol = 'ESH1'
    """
    getdays = "SELECT b.TradingHour\
            FROM [Stock].[dbo].[trading_days_2011] b\
            LEFT JOIN (SELECT [Time] From [Stock].[dbo].[future_onemin] WHERE Symbol = '" + symbol + "') a\
            ON CONVERT(VARCHAR(10),b.TradingHour,111) = CONVERT(VARCHAR(10),a.[Time],111)\
            WHERE a.[Time] IS NULL"
    """
    getdays = "SELECT b.TradingHour\
            FROM [Stock].[dbo].[trading_days_2011] b\
            LEFT JOIN (SELECT [Time] From [Stock].[dbo].[esh1_fifteenmin] WHERE Symbol = '" + symbol + "') a\
            ON CONVERT(VARCHAR(10),b.TradingHour,111) = CONVERT(VARCHAR(10),a.[Time],111)\
            WHERE a.[Time] IS NULL AND CONVERT(VARCHAR(10),b.TradingHour,111) <= '2011/03/19'\
            ORDER BY CONVERT(VARCHAR(10),b.TradingHour,111)"
    cursor.execute(getdays)
    days = cursor.fetchall()

    for day in days:
        endday = str(day[0]).replace('-', '')
        print endday
        con.register(printHistoricalData, message.HistoricalData)
        endday1 = endday.replace('16:00:00', '16:15:00')
        endday2 = endday.replace('16:00:00', '17:30:00')
        con.reqHistoricalData(tickId, futContract, endday1, '1 D', '15 mins', 'TRADES', 0, 1)
        sleep(10)
        con.reqHistoricalData(tickId, futContract, endday2, '1 D', '15 mins', 'TRADES', 0, 1)
        sleep(10)
        con.unregisterAll(printHistoricalData)

    #sleep(10)
    con.disconnect()
    fout.close()
    """
    sqlupdate = "sqlcmd -S BBC-DESKTOP\\BBC -d Stock -Q \
            \"BULK INSERT Stock.dbo.esh1_fivemin\
            FROM \'C:\\Stock\\IBATM\\IB_Hist\\esh1_fifteenmin_temp.txt\' \
            WITH (FIELDTERMINATOR =\',\', \
            ROWTERMINATOR =\'\\n\')\""
    os.system(sqlupdate)
    """
